package org.cgs.web.server.registration;

import java.io.IOException;
import java.sql.*;

import javax.servlet.ServletException;
import javax.servlet.http.*;

import org.apache.log4j.Logger;
import org.cgs.web.database.DatabaseConnector;

public final class AddStudent extends HttpServlet
{
    private static final long serialVersionUID = 1L;
    private static final Logger LOGGER = Logger.getLogger(AddStudent.class);

    @Override
    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {
        // Get student info from web page form
        String courseCode = (String)request.getSession().getAttribute("courseCode");
        boolean isInstructor = request.getParameter("isInstructor") == null ? true : Boolean.parseBoolean(request.getParameter("isInstructor"));
        String number = request.getParameter("studentNumber") == null ? "" : request.getParameter("studentNumber");
        String name = request.getParameter("name") == null ? "" : request.getParameter("name");
        String email = request.getParameter("email") == null ? "" : request.getParameter("email");
        String phone = request.getParameter("phone") == null ? "" : request.getParameter("phone");
        String programAndYear = request.getParameter("programAndYear") == null ? "" : request.getParameter("programAndYear");
        String recentCourse = request.getParameter("recentCourse") == null ? "" : request.getParameter("recentCourse");
        String whyInterested = request.getParameter("whyInterested") == null ? "" : request.getParameter("whyInterested");
        String likeSubject = request.getParameter("likeSubject") == null ? "" : request.getParameter("likeSubject");
        String transportation = request.getParameter("transportation") == null ? "" : request.getParameter("transportation");
        String teachingExp = request.getParameter("teachingExperience") == null ? "" : request.getParameter("teachingExperience");
        String teachingAttr = request.getParameter("teachingAttraction") == null ? "" : request.getParameter("teachingAttraction");
        String[] times = request.getParameterValues("times") == null ? new String[] {} : request.getParameterValues("times");
        String hasVehicle = request.getParameter("hasVehicle") == null ? "" : request.getParameter("hasVehicle");
        String hasTeachingExperience = request.getParameter("hasTeachingExperience") == null ? "" : request.getParameter("hasTeachingExperience");
        String hasSubjectKnowledge = request.getParameter("hasSubjectKnowledge") == null ? "" : request.getParameter("hasSubjectKnowledge");

        // See if the school name exists in the database
        boolean studentExists = false;
        boolean errorOccurred = false;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try
        {
            con = DatabaseConnector.getInstance().getConnection();
            pstmt = con.prepareStatement("SELECT * FROM student WHERE studentNumber=? AND courseCode=?");

            pstmt.setString(1, number);
            pstmt.setString(2, courseCode);
            rs = pstmt.executeQuery();

            // If there is an entry in first spot of result set, school exists
            if (rs.next())
            {
                studentExists = true;
                LOGGER.info("Student " + number + " already exists for course " + courseCode);
            }
            else
            {
                try
                {
                    rs.close();
                }
                catch (Exception e)
                {
                    errorOccurred = true;
                    LOGGER.error("Exception cleaning up result set", e);
                }

                try
                {
                    pstmt.close();
                }
                catch (Exception e)
                {
                    errorOccurred = true;
                    LOGGER.error("Exception cleaning up prepared statement", e);
                }

                pstmt = con.prepareStatement("INSERT INTO student VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                pstmt.setString(1, name);
                pstmt.setString(2, number);
                pstmt.setString(3, courseCode);
                pstmt.setString(4, email);
                pstmt.setString(5, phone);
                pstmt.setString(6, programAndYear);
                pstmt.setString(7, recentCourse);
                pstmt.setString(8, whyInterested);
                pstmt.setString(9, likeSubject);
                pstmt.setString(10, transportation);
                pstmt.setString(11, teachingExp);
                pstmt.setString(12, teachingAttr);

                if (hasVehicle.length() == 0)
                {
                    pstmt.setNull(13, Types.VARCHAR);
                }
                else
                {
                    pstmt.setString(13, hasVehicle);
                }

                if (hasTeachingExperience.length() == 0)
                {
                    pstmt.setNull(14, Types.VARCHAR);
                }
                else
                {
                    pstmt.setString(14, hasTeachingExperience);
                }

                if (hasSubjectKnowledge.length() == 0)
                {
                    pstmt.setNull(15, Types.VARCHAR);
                }
                else
                {
                    pstmt.setString(15, hasSubjectKnowledge);
                }

                pstmt.executeUpdate();

                try
                {
                    // Close the PreparedStatement
                    pstmt.close();
                }
                catch (Exception e)
                {
                    errorOccurred = true;
                    LOGGER.error("Exception cleaning up prepared statement", e);
                }

                // Insert the school times
                pstmt = con.prepareStatement("INSERT INTO student_times VALUES (?, ?, ?)", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
                for (String time : times)
                {
                    pstmt.setString(1, number);
                    pstmt.setString(2, time);
                    pstmt.setString(3, courseCode);
                    pstmt.executeUpdate();
                } // End for loop
            } // End else
        } // End try block
        catch (SQLException e)
        {
            errorOccurred = true;
            LOGGER.error("Exception thrown attempting to add new student", e);
        } // End catch block
        finally
        {
            try
            {
                // Close the ResultSet
                if (rs != null)
                {
                    rs.close();
                }
            }
            catch (Exception e)
            {
                errorOccurred = true;
                LOGGER.error("Exception cleaning up result set", e);
            }

            try
            {
                // Close the PreparedStatement
                if (pstmt != null)
                {
                    pstmt.close();
                }
            }
            catch (Exception e)
            {
                errorOccurred = true;
                LOGGER.error("Exception cleaning up prepared statement", e);
            }

            try
            {
                // Close the connection
                if (con != null)
                {
                    con.close();
                }
            }
            catch (Exception e)
            {
                errorOccurred = true;
                LOGGER.error("Exception cleaning up connection to the database", e);
            }
        }

        if (studentExists)
        {
            request.getRequestDispatcher("").forward(request, response);
        }
        else
        {
            if (errorOccurred)
            {
                request.getRequestDispatcher("").forward(request, response);
            }
            else
            {
                if (isInstructor)
                {
                    response.sendRedirect("instructor/instructorMain.jsp");
                }
                else
                {
                    response.sendRedirect("student/studentMain.jsp");
                }
            }
        }
    } // End method
} // End class
